{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import  sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "host = 'localhost'\n",
    "port = 3306\n",
    "user = 'root'\n",
    "password = '12345'\n",
    "database = 'recommend2'\n",
    "\n",
    "conn2 = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(user,password,host,port,database))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = pd.read_sql(\n",
    "    'select * from salesView',\n",
    "    conn2\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售ID</th>\n",
       "      <th>销售姓名</th>\n",
       "      <th>沟通用户总数</th>\n",
       "      <th>直接成单</th>\n",
       "      <th>跟单成功数</th>\n",
       "      <th>总成单数</th>\n",
       "      <th>直接成单率</th>\n",
       "      <th>跟单成功率</th>\n",
       "      <th>总成功率</th>\n",
       "      <th>非意向用户数</th>\n",
       "      <th>跟单失败率</th>\n",
       "      <th>非意向用户率</th>\n",
       "      <th>跟踪失败率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>刘洋</td>\n",
       "      <td>1073</td>\n",
       "      <td>46</td>\n",
       "      <td>29</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0429</td>\n",
       "      <td>0.0270</td>\n",
       "      <td>0.0699</td>\n",
       "      <td>411</td>\n",
       "      <td>0.1454</td>\n",
       "      <td>0.3830</td>\n",
       "      <td>0.3796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16</td>\n",
       "      <td>刘芳</td>\n",
       "      <td>1089</td>\n",
       "      <td>50</td>\n",
       "      <td>19</td>\n",
       "      <td>69</td>\n",
       "      <td>0.0459</td>\n",
       "      <td>0.0174</td>\n",
       "      <td>0.0634</td>\n",
       "      <td>440</td>\n",
       "      <td>0.1552</td>\n",
       "      <td>0.4040</td>\n",
       "      <td>0.3841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>张丽</td>\n",
       "      <td>1063</td>\n",
       "      <td>43</td>\n",
       "      <td>15</td>\n",
       "      <td>58</td>\n",
       "      <td>0.0405</td>\n",
       "      <td>0.0141</td>\n",
       "      <td>0.0546</td>\n",
       "      <td>572</td>\n",
       "      <td>0.2060</td>\n",
       "      <td>0.5381</td>\n",
       "      <td>0.3829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>张勇</td>\n",
       "      <td>1079</td>\n",
       "      <td>83</td>\n",
       "      <td>38</td>\n",
       "      <td>121</td>\n",
       "      <td>0.0769</td>\n",
       "      <td>0.0352</td>\n",
       "      <td>0.1121</td>\n",
       "      <td>371</td>\n",
       "      <td>0.1288</td>\n",
       "      <td>0.3438</td>\n",
       "      <td>0.3747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>张敏</td>\n",
       "      <td>1075</td>\n",
       "      <td>65</td>\n",
       "      <td>25</td>\n",
       "      <td>90</td>\n",
       "      <td>0.0605</td>\n",
       "      <td>0.0233</td>\n",
       "      <td>0.0837</td>\n",
       "      <td>404</td>\n",
       "      <td>0.1619</td>\n",
       "      <td>0.3758</td>\n",
       "      <td>0.4307</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   销售ID 销售姓名  沟通用户总数  直接成单  跟单成功数  总成单数   直接成单率   跟单成功率    总成功率  非意向用户数  \\\n",
       "0     8   刘洋    1073    46     29    75  0.0429  0.0270  0.0699     411   \n",
       "1    16   刘芳    1089    50     19    69  0.0459  0.0174  0.0634     440   \n",
       "2    24   张丽    1063    43     15    58  0.0405  0.0141  0.0546     572   \n",
       "3     9   张勇    1079    83     38   121  0.0769  0.0352  0.1121     371   \n",
       "4    30   张敏    1075    65     25    90  0.0605  0.0233  0.0837     404   \n",
       "\n",
       "    跟单失败率  非意向用户率   跟踪失败率  \n",
       "0  0.1454  0.3830  0.3796  \n",
       "1  0.1552  0.4040  0.3841  \n",
       "2  0.2060  0.5381  0.3829  \n",
       "3  0.1288  0.3438  0.3747  \n",
       "4  0.1619  0.3758  0.4307  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data['底薪'] = 2000\n",
    "df_data['直接成单奖金'] = 0\n",
    "df_data['跟单成功奖金'] = 0\n",
    "df_data['直接成单数最高者'] = 0\n",
    "df_data['跟单成功率最高着'] = 0\n",
    "df_data['跟单成功数最高着'] = 0\n",
    "df_data['总成单数最高者'] = 0\n",
    "df_data['总成单率最高者'] = 0\n",
    "df_data['总工资'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['销售ID', '销售姓名', '沟通用户总数', '直接成单', '跟单成功数', '总成单数', '直接成单率', '跟单成功率',\n",
       "       '总成功率', '非意向用户数', '跟单失败率', '非意向用户率', '跟踪失败率', '底薪', '直接成单奖金', '跟单成功奖金',\n",
       "       '直接成单数最高者', '跟单成功率最高着', '跟单成功数最高着', '总成单数最高者', '总成单率最高者', '总工资'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 8 8 15 15\n"
     ]
    }
   ],
   "source": [
    "index1 = df_data[\"直接成单\"].sort_values(ascending=False).index.tolist()[0]\n",
    "index2 = df_data[\"跟单成功率\"].sort_values(ascending=False).index.tolist()[0]\n",
    "index3 = df_data[\"跟单成功数\"].sort_values(ascending=False).index.tolist()[0]\n",
    "index4 = df_data[\"总成单数\"].sort_values(ascending=False).index.tolist()[0]\n",
    "index5 = df_data[\"总成功率\"].sort_values(ascending=False).index.tolist()[0]\n",
    "print(index1, index2,index3, index4,index5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data.loc[index1,\"直接成单数最高者\"] = 2000\n",
    "df_data.loc[index2,\"跟单成功率最高着\"] = 1600\n",
    "df_data.loc[index3,\"跟单成功数最高着\"] = 1600\n",
    "df_data.loc[index4,\"总成单数最高者\"] = 3000\n",
    "df_data.loc[index5,\"总成单率最高者\"] = 3000\n",
    "df_data[\"直接成单奖金\"] = df_data[\"直接成单\"]*100\n",
    "df_data[\"跟单成功奖金\"] = df_data[\"跟单成功数\"]*80\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data[\"总工资\"] = df_data['底薪']+df_data[\"直接成单奖金\"] + df_data[\"跟单成功奖金\"] + df_data[\"直接成单数最高者\"] + df_data[\"跟单成功率最高着\"] + df_data[\"跟单成功数最高着\"] + df_data[\"总成单数最高者\"] + df_data[\"总成单率最高者\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售ID</th>\n",
       "      <th>销售姓名</th>\n",
       "      <th>沟通用户总数</th>\n",
       "      <th>直接成单</th>\n",
       "      <th>跟单成功数</th>\n",
       "      <th>总成单数</th>\n",
       "      <th>直接成单率</th>\n",
       "      <th>跟单成功率</th>\n",
       "      <th>总成功率</th>\n",
       "      <th>非意向用户数</th>\n",
       "      <th>...</th>\n",
       "      <th>跟踪失败率</th>\n",
       "      <th>底薪</th>\n",
       "      <th>直接成单奖金</th>\n",
       "      <th>跟单成功奖金</th>\n",
       "      <th>直接成单数最高者</th>\n",
       "      <th>跟单成功率最高着</th>\n",
       "      <th>跟单成功数最高着</th>\n",
       "      <th>总成单数最高者</th>\n",
       "      <th>总成单率最高者</th>\n",
       "      <th>总工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>刘洋</td>\n",
       "      <td>1073</td>\n",
       "      <td>46</td>\n",
       "      <td>29</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0429</td>\n",
       "      <td>0.0270</td>\n",
       "      <td>0.0699</td>\n",
       "      <td>411</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3796</td>\n",
       "      <td>2000</td>\n",
       "      <td>4600</td>\n",
       "      <td>2320</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16</td>\n",
       "      <td>刘芳</td>\n",
       "      <td>1089</td>\n",
       "      <td>50</td>\n",
       "      <td>19</td>\n",
       "      <td>69</td>\n",
       "      <td>0.0459</td>\n",
       "      <td>0.0174</td>\n",
       "      <td>0.0634</td>\n",
       "      <td>440</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3841</td>\n",
       "      <td>2000</td>\n",
       "      <td>5000</td>\n",
       "      <td>1520</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>张丽</td>\n",
       "      <td>1063</td>\n",
       "      <td>43</td>\n",
       "      <td>15</td>\n",
       "      <td>58</td>\n",
       "      <td>0.0405</td>\n",
       "      <td>0.0141</td>\n",
       "      <td>0.0546</td>\n",
       "      <td>572</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3829</td>\n",
       "      <td>2000</td>\n",
       "      <td>4300</td>\n",
       "      <td>1200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>张勇</td>\n",
       "      <td>1079</td>\n",
       "      <td>83</td>\n",
       "      <td>38</td>\n",
       "      <td>121</td>\n",
       "      <td>0.0769</td>\n",
       "      <td>0.0352</td>\n",
       "      <td>0.1121</td>\n",
       "      <td>371</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3747</td>\n",
       "      <td>2000</td>\n",
       "      <td>8300</td>\n",
       "      <td>3040</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>张敏</td>\n",
       "      <td>1075</td>\n",
       "      <td>65</td>\n",
       "      <td>25</td>\n",
       "      <td>90</td>\n",
       "      <td>0.0605</td>\n",
       "      <td>0.0233</td>\n",
       "      <td>0.0837</td>\n",
       "      <td>404</td>\n",
       "      <td>...</td>\n",
       "      <td>0.4307</td>\n",
       "      <td>2000</td>\n",
       "      <td>6500</td>\n",
       "      <td>2000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   销售ID 销售姓名  沟通用户总数  直接成单  跟单成功数  总成单数   直接成单率   跟单成功率    总成功率  非意向用户数  ...  \\\n",
       "0     8   刘洋    1073    46     29    75  0.0429  0.0270  0.0699     411  ...   \n",
       "1    16   刘芳    1089    50     19    69  0.0459  0.0174  0.0634     440  ...   \n",
       "2    24   张丽    1063    43     15    58  0.0405  0.0141  0.0546     572  ...   \n",
       "3     9   张勇    1079    83     38   121  0.0769  0.0352  0.1121     371  ...   \n",
       "4    30   张敏    1075    65     25    90  0.0605  0.0233  0.0837     404  ...   \n",
       "\n",
       "    跟踪失败率    底薪  直接成单奖金  跟单成功奖金  直接成单数最高者  跟单成功率最高着  跟单成功数最高着  总成单数最高者  \\\n",
       "0  0.3796  2000    4600    2320         0         0         0        0   \n",
       "1  0.3841  2000    5000    1520         0         0         0        0   \n",
       "2  0.3829  2000    4300    1200         0         0         0        0   \n",
       "3  0.3747  2000    8300    3040         0         0         0        0   \n",
       "4  0.4307  2000    6500    2000         0         0         0        0   \n",
       "\n",
       "   总成单率最高者    总工资  \n",
       "0        0   8920  \n",
       "1        0   8520  \n",
       "2        0   7500  \n",
       "3        0  13340  \n",
       "4        0  10500  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data.to_sql(\n",
    "    '2020-07money',\n",
    "    conn2,\n",
    "    index=False,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
